-- Table: public.ma_department

-- DROP TABLE public.ma_department;

CREATE TABLE public.ma_department
(
  did serial primary key, -- ID 主键 自增
  p_id integer NOT NULL DEFAULT 0, -- 上一层 0 表示顶层
  create_uid character varying(50) NOT NULL,
  department_name text NOT NULL,
  leader_name character varying(50),
  leader_tel character varying(50),
  crtime timestamp without time zone DEFAULT now(),
  last_modify_time timestamp without time zone DEFAULT now(),
  description text 
) ;
COMMENT ON TABLE public.ma_department
  IS '组织结构,有层级关系和层级类型,如果已经是项目组,则不允许有下一层单位';
COMMENT ON COLUMN public.ma_department.did IS 'ID 主键 自增';
COMMENT ON COLUMN public.ma_department.p_id IS '上一层 0 表示顶层';




-- Table: public.ma_user

-- DROP TABLE public.ma_user;

CREATE TABLE public.ma_user
(
   id serial primary key,
  user_id character varying(50) NOT NULL, -- 用户ID 目前采用邮箱
  user_name character varying(50), -- 员工姓名
  user_pass character varying(50), -- 密码 MD5加密之后
  create_time timestamp without time zone DEFAULT now(), -- 创建时间 默认当前时间
  roles text, -- 角色 ,分割
  dept_id integer,  -- 归属部门
  description text, 
  CONSTRAINT user_id_unique UNIQUE (user_id) -- 登录ID 唯一约束
);
COMMENT ON COLUMN public.ma_user.user_id IS '用户ID 目前采用邮箱';
COMMENT ON COLUMN public.ma_user.user_name IS '员工姓名';
COMMENT ON COLUMN public.ma_user.user_pass IS '密码 MD5加密之后';
COMMENT ON COLUMN public.ma_user.create_time IS '创建时间 默认当前时间';
COMMENT ON COLUMN public.ma_user.roles IS '角色 ,分割';



-- Table: public.ma_menu

-- DROP TABLE public.ma_menu;

CREATE TABLE public.ma_menu
(
  menu_id character varying(50) NOT NULL,
  parent_id character varying(50) DEFAULT 0, -- 上级菜单ID
  menu_name character varying(50),
  menu_url text,
  menu_icon character varying(200),
  menu_type integer DEFAULT 1, -- 菜单类型, 1- 可显示菜单,2-权限URL
  is_visible boolean DEFAULT true, -- 是否显示,menu_type=2的URL 都是不可显示的
  CONSTRAINT hl_menu_pkey PRIMARY KEY (menu_id)
) ;
COMMENT ON COLUMN public.ma_menu.parent_id IS '上级菜单ID';
COMMENT ON COLUMN public.ma_menu.menu_type IS '菜单类型, 1- 可显示菜单,2-权限URL';
COMMENT ON COLUMN public.ma_menu.is_visible IS '是否显示,menu_type=2的URL 都是不可显示的';



INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_updateRole', 'ma_roles', '编辑角色名称', './ma/updateRole', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_removeMenu', 'ma_menus', '删除菜单', './ma/removeMenu', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_addMenu', 'ma_menus', '添加菜单', './ma/addMenu', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma', NULL, '综合管理', NULL, NULL, 1, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_findRole', 'ma_roles', '获取角色列表', './ma/findRoles', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_resetPass', 'ma_users', '密码重置', './ma/resetPass', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_updateMenu', 'ma_menus', '编辑菜单', './ma/updateMenu', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_editRolePerms', 'ma_roles', '更新角色权限', './ma/editRolePerms', '', 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('index', NULL, '测试', NULL, NULL, 1, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_findMenus', 'ma_menus', '获取菜单列表', './ma/findMenus', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_menus', 'base_ma', '菜单信息', './ma/menus', NULL, 1, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_depts', 'base_ma', '机构信息', './ma/depts', NULL, 1, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_roles', 'base_ma', '角色信息', './ma/roles', '&#xe70d;', 1, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_users', 'base_ma', '用户信息', './ma/users', '&#xe611;', 1, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('base_ma', 'ma', '基础数据', NULL, '&#xe611;', 1, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('logger_ma', 'ma', '日志信息', NULL, NULL, 1, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_logs', 'logger_ma', '访问日志', './logger/logs', NULL, 1, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('logger_findLogs', 'ma_logs', '日志明细查询', './logger/findLogs', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('logger_exportLog', 'ma_logs', '日志明细导出', './logger/exportCallInfo', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_findUser', 'ma_users', '获取用户列表', './ma/findUsers', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_updateUser', 'ma_users', '用户信息更新', './ma/updateUser', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_removeRole', 'ma_roles', '删除角色', './ma/removeRole', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_addUser', 'ma_users', '添加用户', './ma/addUser', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_removeUser', 'ma_users', '删除用户', './ma/removeUser', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_addDept', 'ma_depts', '机构添加', './ma/addDept', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_removeDept', 'ma_depts', '机构移除', './ma/removeDept', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_updateDept', 'ma_depts', '机构更新', './ma/updateDept', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_addRole', 'ma_roles', '添加角色', './ma/addRole', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_menusRole', 'ma_roles', '角色权限', './ma/findRoleMenus', NULL, 2, true);
INSERT INTO ma_menu (menu_id, parent_id, menu_name, menu_url, menu_icon, menu_type, is_visible) VALUES ('ma_findDepts', 'ma_depts', '机构查询', './ma/findDepts', NULL, 2, true);


-- Table: public.ma_role

-- DROP TABLE public.ma_role;

CREATE TABLE public.ma_role
(
  role_id serial primary key,
  role_name character varying(50),
  default_role boolean DEFAULT false,
  crtime timestamp without time zone DEFAULT now(),
  perms text  -- 权限 ,分割 
) ;
COMMENT ON COLUMN public.ma_role.perms IS '权限 ,分割';



-- Table: public.info_log

-- DROP TABLE public.info_log;

CREATE TABLE public.info_log
(
  id serial primary key,
  uid integer,
  user_name character varying(50) DEFAULT ''::character varying,
  model character varying(50) DEFAULT ''::character varying,
  name character varying(50) DEFAULT ''::character varying,
  content text DEFAULT ''::text,
  optype character varying(50) DEFAULT ''::character varying,
  result text DEFAULT ''::text,
  exception text DEFAULT ''::text,
  crtime timestamp without time zone NOT NULL DEFAULT now() 
) ;





-- Function: public.get_all_childs_did(integer)

-- DROP FUNCTION public.get_all_childs_did(integer);

CREATE OR REPLACE FUNCTION public.get_all_childs_did(_p_id integer)
  RETURNS integer[] AS
$BODY$
declare
	
	_childs int[] ;

begin
	if _p_id is null then return null ; end if ;

	if _p_id = 0 then  
		select array_agg(did) from ma_department into _childs ; 
		return _childs;
	end if ;

	if _p_id > 0 then
		with RECURSIVE t1(did , p_id) as (
			select did,p_id from ma_department where did = _p_id  
			union all select t2.did , t2.p_id from t1 , ma_department as t2 where t1.did = t2.p_id 
			)  
		select array_agg(did) from  t1 into _childs ;
		return _childs;
		
	end if ;
	
	return null ;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 
  
  
  -- Function: public.get_user_rolenames(integer)

-- DROP FUNCTION public.get_user_rolenames(integer);

CREATE OR REPLACE FUNCTION public.get_user_rolenames(_uid integer)
  RETURNS text AS
$BODY$
declare
	 
	_roles text ;
	_names text ;
begin
	select roles from ma_user where id = $1 into _roles;

	if _roles is null or length(_roles) < 1 then
		return  '';
	end if;

	select string_agg(role_name , ',') as names from ma_role  where role_id in ( select regexp_split_to_table(_roles , ',')::int role_id )  into _names ;

	return _names ;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100; 



